ALTER TABLE Business
ADD Gmt INT NOT NULL DEFAULT(0)
GO
ALTER PROCEDURE [dbo].[spAPI_Business_Register]
	@login varchar(50)
	,@password varchar(50)
	,@email varchar(50)
	,@businessName varchar(100)
	,@web varchar(100)
	,@businessPhone varchar(50)
	,@cellPhone varchar(50)
	,@tollFreePhone varchar(50)
	,@fax varchar(50)
	,@streetAddress varchar(100)
	,@streetAddress2 varchar(100)
	,@city varchar(50)
	,@state varchar(2)
	,@zip varchar(5)
	,@description varchar(MAX)
	,@referencedBy varchar(100)
	,@sunFrom datetime
	,@sunTo datetime
	,@sunClosed bit
	,@monFrom datetime
	,@monTo datetime
	,@monClosed bit
	,@tueFrom datetime
	,@tueTo datetime
	,@tueClosed bit
	,@wedFrom datetime
	,@wedTo datetime
	,@wedClosed bit
	,@thuFrom datetime
	,@thuTo datetime
	,@thuClosed bit
	,@friFrom datetime
	,@friTo datetime
	,@friClosed bit
	,@satFrom datetime
	,@satTo datetime
	,@satClosed bit
	,@categories varchar(max)
	,@newCategory1 varchar(250)
	,@newCategory2 varchar(250)
	,@newCategory3 varchar(250)
	,@latitude float
	,@longitude float
	,@gmt int = 0
	,@creditCardNo varchar(20)
	,@creditCardExpDate datetime
	,@creditCardName varchar(50)
	,@creditCardPin varchar(10)
	,@useBusinessAddress bit
	,@billingStreetAddress varchar(100)
	,@billingStreetAddress2 varchar(100)
	,@billingCity varchar(50)
	,@billingState varchar(2)
	,@billingZip varchar(5)
	
	,@PictureContentType VARCHAR(50) = ''
	,@Picture varbinary(max) = null	
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	DECLARE @IdResult INT
	SET @IdResult = 1
	
	IF ISNULL(@referencedBy, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM UserBusinessLogins WHERE LoginName = @referencedBy)
		BEGIN
			SET @IdResult = 0
			SELECT @IdResult AS IdResult
			RETURN
		END

	IF ISNULL(@zip, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode WHERE ZipCode = @zip)
		BEGIN
			SET @IdResult = -3
			SELECT @IdResult AS IdResult
			RETURN
		END

	IF ISNULL(@billingZip, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode WHERE ZipCode = @billingZip)
		BEGIN
			SET @IdResult = -4
			SELECT @IdResult AS IdResult
			RETURN
		END
		
	IF ISNULL(@Email, '') <> ''
		IF EXISTS(SELECT 1 FROM [User] (NOLOCK) WHERE Email = @Email) OR
			EXISTS(SELECT 1 FROM Business (NOLOCK) WHERE Email = @Email)
		BEGIN
			SET @IdResult = -2
			SELECT @IdResult AS IdResult
			RETURN
		END
	
	BEGIN TRANSACTION
	
		INSERT INTO UserBusinessLogins(LoginName, LoginType)
		VALUES(@login, 'B')
		if @@error <> 0 return
	
		INSERT INTO [Business]
			([Login]
			,[Email]
			,[Password]
			,[BusinessName]
			,[Web]
			,[BusinessPhone]
			,[CellPhone]
			,[TollFreePhone]
			,[Fax]
			,[StreetAddress1]
			,[StreetAddress2]
			,[IsActive]
			,[Deleted]
			,[City]
			,[StateCode]
			,[ZipCode]
			,[Description]
			,[CreditCardNo]
			,[CreditCardExpDate]
			,[CreditCardName]
			,[CreditCardPin]
			,[BillingAddress1]
			,[BillingAddress2]
			,[BillingCity]
			,[BillingStateCode]
			,[BillingZipCode]
			,[BillingUse]
			,[ReferencedBy]
			,[Latitude]
			,[Longitude]
			,Gmt
			,PictureContentType
			,Picture)
		 VALUES
			(@Login
			,@Email
			,@Password
			,@BusinessName
			,@Web
			,@BusinessPhone
			,@CellPhone
			,@TollFreePhone
			,@Fax
			,@StreetAddress
			,@StreetAddress2
			,1
			,0
			,@City
			,@State
			,@Zip
			,@Description
			,@CreditCardNo
			,@CreditCardExpDate
			,@CreditCardName
			,@CreditCardPin
			,@BillingStreetAddress
			,@BillingStreetAddress2
			,@BillingCity
			,@BillingState
			,@BillingZip
			,@useBusinessAddress
			,@ReferencedBy
			,@Latitude
			,@Longitude
			,@gmt
			,@PictureContentType
			,@Picture)
		declare @businessId int = SCOPE_IDENTITY()
		
		
		INSERT INTO BusinessHours(BusinessId, [WeekDay], FromTime, ToTime, Closed)
		values
			(@BusinessId, 'Sun', @SunFrom, @SunTo, @SunClosed)
			,(@BusinessId, 'Mon', @MonFrom, @MonTo, @MonClosed)
			,(@BusinessId, 'Tue', @TueFrom, @TueTo, @TueClosed)
			,(@BusinessId, 'Wed', @WedFrom, @WedTo, @WedClosed)
			,(@BusinessId, 'Thu', @ThuFrom, @ThuTo, @ThuClosed)
			,(@BusinessId, 'Fri', @FriFrom, @FriTo, @FriClosed)
			,(@BusinessId, 'Sat', @SatFrom, @SatTo, @SatClosed)
    
    
		INSERT INTO BusinessCategory(BusinessId, CategoryId)
		SELECT @BusinessId, CONVERT(INT, Items.Item)
		FROM dbo.fnAPI_String_Split(@Categories, ',') AS Items
		
		IF (@newCategory1 IS NOT NULL)
			IF (@newCategory1 <> '')
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory1)
		IF (@newCategory2 IS NOT NULL)
			IF (@newCategory2 <> '')
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory2)
		IF (@newCategory3 IS NOT NULL)
			IF (@newCategory3 <> '')
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory3)
						
		declare @SecurityToken uniqueidentifier = NEWID()
					
		INSERT INTO UserToken(SecurityToken, LoginName, ExpirationDate)
		VALUES(@SecurityToken, @Login, '99991231')
		
		SELECT @IdResult AS IdResult, @SecurityToken AS SecurityToken

	COMMIT TRANSACTION
END
GO
ALTER PROCEDURE [dbo].[spAPI_Business_Update]
	@Token VARCHAR(255)
	,@password varchar(50)
	,@email varchar(50)
	,@businessName varchar(100)
	,@web varchar(100)
	,@businessPhone varchar(50)
	,@cellPhone varchar(50)
	,@tollFreePhone varchar(50)
	,@fax varchar(50)
	,@streetAddress varchar(100)
	,@streetAddress2 varchar(100)
	,@city varchar(50)
	,@state varchar(2)
	,@zip varchar(50)
	,@description varchar(MAX)
	,@referencedBy varchar(100)
	,@sunFrom datetime
	,@sunTo datetime
	,@sunClosed bit
	,@monFrom datetime
	,@monTo datetime
	,@monClosed bit
	,@tueFrom datetime
	,@tueTo datetime
	,@tueClosed bit
	,@wedFrom datetime
	,@wedTo datetime
	,@wedClosed bit
	,@thuFrom datetime
	,@thuTo datetime
	,@thuClosed bit
	,@friFrom datetime
	,@friTo datetime
	,@friClosed bit
	,@satFrom datetime
	,@satTo datetime
	,@satClosed bit
	,@categories varchar(max)
	,@newCategory1 varchar(250)
	,@newCategory2 varchar(250)
	,@newCategory3 varchar(250)
	,@latitude float
	,@longitude float
	,@gmt int = 0
	,@creditCardNo varchar(20)
	,@creditCardExpDate datetime
	,@creditCardName varchar(50)
	,@creditCardPin varchar(10)
	,@useBusinessAddress bit
	,@billingStreetAddress varchar(100)
	,@billingStreetAddress2 varchar(100)
	,@billingCity varchar(50)
	,@billingState varchar(2)
	,@billingZip varchar(5)
	
	,@PictureContentType VARCHAR(50) = ''
	,@Picture varbinary(max) = null	
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT

	IF @IdResult = 1	
	begin
		IF ISNULL(@zip, '') <> ''
			IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode WHERE ZipCode = @zip)
			BEGIN
				SET @IdResult = -3
				SELECT @IdResult AS IdResult
				RETURN
			END

		IF ISNULL(@billingZip, '') <> ''
			IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode WHERE ZipCode = @billingZip)
			BEGIN
				SET @IdResult = -4
				SELECT @IdResult AS IdResult
				RETURN
			END
	
		declare @oldEmail varchar(100) = (select email from Business (nolock) where BusinessId = @BusinessId)
		IF (ISNULL(@Email, '') <> '') and @email <> @oldEmail
		begin
			IF EXISTS(SELECT 1 FROM [User] (NOLOCK) WHERE Email = @Email) OR
				EXISTS(SELECT 1 FROM Business (NOLOCK) WHERE Email = @Email)
			BEGIN
				SET @IdResult = -2
				SELECT @IdResult AS IdResult
				RETURN
			END
		end
	
		BEGIN TRANSACTION		
			update [Business]
			set
				[Email] = @Email
				,[Password] = @Password
				,[BusinessName] = @BusinessName
				,[Web] = @Web
				,[BusinessPhone] = @BusinessPhone
				,[CellPhone] = @CellPhone
				,[TollFreePhone] = @TollFreePhone
				,[Fax] = @Fax
				,[StreetAddress1] = @StreetAddress
				,[StreetAddress2] = @StreetAddress2
				,[City] = @City
				,[StateCode] = @State
				,[ZipCode] = @Zip
				,[Description] = @Description
				,[CreditCardNo] = @CreditCardNo
				,[CreditCardExpDate] = @CreditCardExpDate
				,[CreditCardName] = @CreditCardName
				,[CreditCardPin] = @CreditCardPin
				,[BillingAddress1] = @BillingStreetAddress
				,[BillingAddress2] = @BillingStreetAddress2
				,[BillingCity] = @BillingCity
				,[BillingStateCode] = @BillingState
				,[BillingZipCode] = @BillingZip
				,[BillingUse] = @useBusinessAddress
				,[ReferencedBy] = @ReferencedBy
				,[Latitude] = @Latitude
				,[Longitude] = @Longitude
				,Gmt = @gmt
				,PictureContentType = @PictureContentType
				,Picture = @Picture
			where businessId = @businessId
			
			
			delete from BusinessHours where businessId = @businessId
			
			INSERT INTO BusinessHours(BusinessId, [WeekDay], FromTime, ToTime, Closed)
			values
				(@BusinessId, 'Sun', @SunFrom, @SunTo, @SunClosed)
				,(@BusinessId, 'Mon', @MonFrom, @MonTo, @MonClosed)
				,(@BusinessId, 'Tue', @TueFrom, @TueTo, @TueClosed)
				,(@BusinessId, 'Wed', @WedFrom, @WedTo, @WedClosed)
				,(@BusinessId, 'Thu', @ThuFrom, @ThuTo, @ThuClosed)
				,(@BusinessId, 'Fri', @FriFrom, @FriTo, @FriClosed)
				,(@BusinessId, 'Sat', @SatFrom, @SatTo, @SatClosed)
	    
	    
			delete from BusinessCategory where businessId = @businessId
			
			INSERT INTO BusinessCategory(BusinessId, CategoryId)
			SELECT @BusinessId, CONVERT(INT, Items.Item)
			FROM dbo.fnAPI_String_Split(@Categories, ',') AS Items
			
			
			IF isnull(@newCategory1, '') <> ''
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory1)
			IF isnull(@newCategory2, '') <> ''
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory2)
			IF isnull(@newCategory3, '') <> ''
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory3)
							
		COMMIT TRANSACTION
	end
		
	SELECT @IdResult AS IdResult
END
GO
ALTER PROCEDURE [dbo].[spAPI_Business_RegisterStep1]
	@login varchar(50)
	,@password varchar(50)
	,@email varchar(50)
	,@businessName varchar(100)
	,@web varchar(100)
	,@businessPhone varchar(50)
	,@cellPhone varchar(50)
	,@tollFreePhone varchar(50)
	,@fax varchar(50)
	,@streetAddress varchar(100)
	,@streetAddress2 varchar(100)
	,@city varchar(50)
	,@state varchar(2)
	,@zip varchar(5)
	,@latitude float = null
	,@longitude float = null
	,@gmt int = 0
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	DECLARE @IdResult INT
	SET @IdResult = 1
	
	IF ISNULL(@zip, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode WHERE ZipCode = @zip)
		BEGIN
			SET @IdResult = -3
			SELECT @IdResult AS IdResult
			RETURN
		END
	
	IF ISNULL(@Email, '') <> ''
		IF EXISTS(SELECT 1 FROM [User] (NOLOCK) WHERE Email = @Email) OR
			EXISTS(SELECT 1 FROM Business (NOLOCK) WHERE Email = @Email)
		BEGIN
			SET @IdResult = -2
			SELECT @IdResult AS IdResult
			RETURN
		END
	
	BEGIN TRANSACTION
	
		INSERT INTO UserBusinessLogins(LoginName, LoginType)
		VALUES(@login, 'B')
		if @@error <> 0 return
	
		INSERT INTO [Business]
			([Login]
			,[Email]
			,[Password]
			,[BusinessName]
			,[Web]
			,[BusinessPhone]
			,[CellPhone]
			,[TollFreePhone]
			,[Fax]
			,[StreetAddress1]
			,[StreetAddress2]
			,[IsActive]
			,[Deleted]
			,[City]
			,[StateCode]
			,[ZipCode]
			,[Latitude]
			,[Longitude]
			,Gmt)
		 VALUES
			(@Login
			,@Email
			,@Password
			,@BusinessName
			,@Web
			,@BusinessPhone
			,@CellPhone
			,@TollFreePhone
			,@Fax
			,@StreetAddress
			,@StreetAddress2
			,1
			,0
			,@City
			,@State
			,@Zip
			,@Latitude
			,@Longitude
			,@gmt)		

		declare @SecurityToken uniqueidentifier = NEWID()
					
		INSERT INTO UserToken(SecurityToken, LoginName, ExpirationDate)
		VALUES(@SecurityToken, @Login, '99991231')
		
		SELECT @IdResult AS IdResult, @SecurityToken AS SecurityToken

	COMMIT TRANSACTION
END
GO
ALTER PROCEDURE [dbo].[spAPI_Business_RegisterStep1Update]
	@Token VARCHAR(255)
	,@password varchar(50)
	,@email varchar(50)
	,@businessName varchar(100)
	,@web varchar(100)
	,@businessPhone varchar(50)
	,@cellPhone varchar(50)
	,@tollFreePhone varchar(50)
	,@fax varchar(50)
	,@streetAddress varchar(100)
	,@streetAddress2 varchar(100)
	,@city varchar(50)
	,@state varchar(2)
	,@zip varchar(5)
	,@latitude float = null
	,@longitude float = null
	,@gmt int = 0
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON

	DECLARE @BusinessId INT		
	DECLARE @IdResult INT
	SET @IdResult = 1
	
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	if @IdResult = 0
	begin
		SELECT @IdResult AS IdResult
		return
	end
	
	IF ISNULL(@zip, '') <> ''
		IF NOT EXISTS(SELECT 1 FROM ZatifyGEO..ZipCode WHERE ZipCode = @zip)
		BEGIN
			SET @IdResult = -3
			SELECT @IdResult AS IdResult
			RETURN
		END
	
	declare @oldEmail varchar(100) = (select email from Business (nolock) where BusinessId = @BusinessId)
	IF (ISNULL(@Email, '') <> '') and @email <> @oldEmail
	begin
		IF EXISTS(SELECT 1 FROM [User] (NOLOCK) WHERE Email = @Email) OR
			EXISTS(SELECT 1 FROM Business (NOLOCK) WHERE Email = @Email)
		BEGIN
			SET @IdResult = -2
			SELECT @IdResult AS IdResult
			RETURN
		END
	end

	update [Business]
	set
		[Email] = @Email
		,[Password] = @Password
		,[BusinessName] = @BusinessName
		,[Web] = @Web
		,[BusinessPhone] = @BusinessPhone
		,[CellPhone] = @CellPhone
		,[TollFreePhone] = @TollFreePhone
		,[Fax] = @Fax
		,[StreetAddress1] = @StreetAddress
		,[StreetAddress2] = @StreetAddress2
		,[City] = @City
		,[StateCode] = @State
		,[ZipCode] = @Zip
		,[Latitude] = @Latitude
		,[Longitude] = @Longitude
		,[Gmt] = @gmt
	where BusinessId = @BusinessId
	
	SELECT 1 AS IdResult
END
GO
ALTER PROCEDURE [dbo].[spPromotion_StartStop]
(
	@PromotionId INT = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @Now DATETIME
	SET @Now = GETUTCDATE()
	
	CREATE TABLE #tmpPromotion1
	(
		PromotionId INT,
		Gmt INT
	)
	
	CREATE TABLE #tmpPromotion2
	(
		PromotionId INT,
		Gmt INT
	)
	
	IF @PromotionId IS NOT NULL
		INSERT INTO #tmpPromotion1
		(PromotionId, Gmt)
		SELECT Promotion.PromotionId, Business.Gmt
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE (Promotion.PromotionId = @PromotionId)
			AND (Promotion.StatusCode = 'PEN')
			AND (Promotion.Deleted = 0)
			AND (Promotion.Ready = 1)
			AND (Promotion.BaseCostTimingId IS NOT NULL)
	ELSE
		INSERT INTO #tmpPromotion1
		(PromotionId, Gmt)
		SELECT Promotion.PromotionId, Business.Gmt
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE (Promotion.StatusCode = 'PEN')
			AND (Promotion.Deleted = 0)
			AND (Promotion.Ready = 1)
			AND (Promotion.BaseCostTimingId IS NOT NULL)
			
	-- Starts promotion
	UPDATE Promotion
	SET StatusCode = 'ACT'
	FROM #tmpPromotion1
	WHERE (Promotion.PromotionId = #tmpPromotion1.PromotionId)
		AND (
				-- 2, 4 - starts now
				(Promotion.BaseCostTimingId IN (2, 4)) OR 
				-- 3 - shedule at
				((Promotion.BaseCostTimingId = 3) AND (Promotion.BaseCostTimingScheduledAt <= DATEADD(HOUR, #tmpPromotion1.Gmt, @Now))) OR
				-- 5 - shedule date range
				((Promotion.BaseCostTimingId = 5) AND (Promotion.BaseCostTimingStarts <= DATEADD(HOUR, #tmpPromotion1.Gmt, @Now)))
			)

	-- Make pay for starts promotion
	INSERT INTO PromotionCost
	(PromotionId, DateSubmit, FixedCost)
	SELECT Promotion.PromotionId, GETUTCDATE(), Promotion.FixedCost
	FROM Promotion (NOLOCK) INNER JOIN #tmpPromotion1 ON Promotion.PromotionId = #tmpPromotion1.PromotionId
	WHERE -- 2, 4 - starts now
		(Promotion.BaseCostTimingId IN (2, 4)) OR 
		-- 3 - shedule at
		((Promotion.BaseCostTimingId = 3) AND (Promotion.BaseCostTimingScheduledAt <= DATEADD(HOUR, #tmpPromotion1.Gmt, @Now))) OR
		-- 5 - shedule date range
		((Promotion.BaseCostTimingId = 5) AND (Promotion.BaseCostTimingStarts <= DATEADD(HOUR, #tmpPromotion1.Gmt, @Now)))
				
	IF @PromotionId IS NOT NULL
		INSERT INTO #tmpPromotion2
		(PromotionId, Gmt)
		SELECT Promotion.PromotionId, Business.Gmt
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE (PromotionId = @PromotionId)
			AND (Promotion.StatusCode = 'ACT')
			AND (Promotion.Deleted = 0)
			AND (Promotion.Ready = 1)
			AND (Promotion.BaseCostTimingId IS NOT NULL)
	ELSE
		INSERT INTO #tmpPromotion2
		(PromotionId, Gmt)
		SELECT Promotion.PromotionId, Business.Gmt
		FROM Promotion (NOLOCK) INNER JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
		WHERE (Promotion.StatusCode = 'ACT')
			AND (Promotion.Deleted = 0)
			AND (Promotion.Ready = 1)
			AND (Promotion.BaseCostTimingId IS NOT NULL)
	
	-- Stop promotion
	UPDATE Promotion
	SET StatusCode = 'COM'			
	FROM #tmpPromotion2
	WHERE (Promotion.PromotionId = #tmpPromotion2.PromotionId)
		AND (Promotion.StatusCode = 'ACT')
		AND (Promotion.BaseCostTimingId = 5) 
		AND ((Promotion.BaseCostTimingEnds <= DATEADD(HOUR, #tmpPromotion2.Gmt, @Now)) or (DATEDIFF(DAY, Promotion.BaseCostTimingStarts, GETDATE()) > 7))

	UPDATE Promotion
	SET StatusCode = 'COM'			
	FROM #tmpPromotion2
	WHERE (Promotion.PromotionId = #tmpPromotion2.PromotionId)
		AND (Promotion.StatusCode = 'ACT')
		AND (Promotion.BaseCostTimingId  IN (2, 3, 4)) 
		AND (DATEDIFF(DAY, Promotion.BaseCostTimingScheduledAt, GETDATE()) > 7)


	DROP TABLE #tmpPromotion1
	DROP TABLE #tmpPromotion2
END
GO